<?php

namespace App\Console\Commands;

use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\DB;

class DailyUnionRoomData extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'DailyUnionRoomData';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '每日统计房间消费流水';
    protected $connection = 'api';

    protected $git_stream_table = 'gift_stream';//送礼流水表
    protected $roomTable = 'room';//房间表
    protected $roomUnionTable = 'union_room';//公会房间表
    protected $unionTable = 'union';//公会表
    protected $userRegisterTable = 'user_register_log';//用户注册表
    protected $dailyRoomDataTable = 'daily_room_data';//每日统计表

    protected $errorLog = 'error_dailyunionroom';
    protected $log = 'dailyunionroom';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $startYestorday = Carbon::yesterday()->startOfDay()->toDateTimeString();//开始时间
        $endYestorday = Carbon::yesterday()->endOfDay()->toDateTimeString();//结束时间

        //查询对应送礼流水记录
        $alldata = DB::connection($this->connection)->table($this->git_stream_table)
                ->select(DB::raw('room_id,MAX(change_timestamp) as change_timestamp,sum(gift_price*gift_num) as totalDiamond, count(distinct(from_user_id)) as totalPeople'))
                ->whereIn('type', [1, 2])
                ->where('room_id','>',0)
                ->where('change_timestamp', '>=', strtotime($startYestorday))
                ->where('change_timestamp', '<=', strtotime($endYestorday))
                ->groupBy('room_id')
                ->get();
        $alldata = objToArray($alldata);

        if(!empty($alldata)){
            try {
                //整理房间id
                $allRoomIdArr = array_column($alldata,'room_id');

                //查询房间信息
                $roomInfoArr = DB::connection($this->connection)->table($this->roomTable)->whereIn('room_id',$allRoomIdArr)->get(['name','room_id']);
                $roomInfoArr = objToArray($roomInfoArr);
                $roomInfoArr = array_column($roomInfoArr,null,'room_id');

                //公会名称
                $roomBlongUnion = DB::connection($this->connection)->table($this->roomUnionTable)->whereIn('room_id',$allRoomIdArr)->get(['un_id','room_id']);
                $roomBlongUnion = objToArray($roomBlongUnion);
                //整理公会id
                $unionIdArr = array_column($roomBlongUnion,'un_id');
                $unionRoomArr = array_column($roomBlongUnion,null,'room_id');

                //查询公会信息
                $uninInfoArr = DB::connection($this->connection)->table($this->unionTable)->whereIn('id',$unionIdArr)->get(['name','id','un_user_id']);
                $uninInfoArr = objToArray($uninInfoArr);
                $uninInfoArr = array_column($uninInfoArr,null,'id');
                

                //重新整理数据
                foreach($alldata as &$r){
                    //统计房间消费用户来源IOS/安卓
                    $allUser = DB::connection($this->connection)->table($this->git_stream_table)
                    ->whereIn('type', [1, 2])
                    ->where('change_timestamp', '>=', strtotime($startYestorday))
                    ->where('change_timestamp', '<=', strtotime($endYestorday))
                    ->where('room_id',$r['room_id'])
                    ->get(['room_id','from_user_id']);
                    $allUser = objToArray($allUser);
                    //整理送礼用户id
                    $allUserIds = array_column($allUser,'from_user_id');

                    //获取用户注册设备信息
                    $registerFrom = DB::connection($this->connection)->table($this->userRegisterTable)
                            ->whereIn('user_id', $allUserIds)
                            ->where('os_name', 'Android')
                            ->select('user_id')
                            ->distinct()
                            ->get();
                    $userAndroid = objToArray($registerFrom);
                    $userAndroid = array_column($userAndroid,'user_id');
                    $useNumAndroid = count($userAndroid);
                    //获取安卓用户消费总钻石数
                    $allAndroidDiamond = DB::connection($this->connection)->table($this->git_stream_table)
                            ->select(DB::raw('SUM(gift_price*gift_num) as androidDiamond'))
                            ->whereIn('type', [1, 2])
                            ->whereIn('from_user_id',$userAndroid)
                            ->where('room_id',$r['room_id'])
                            ->where('change_timestamp', '>=', strtotime($startYestorday))
                            ->where('change_timestamp', '<=', strtotime($endYestorday))
                            ->get();
                    $allAndroidDiamond = objToArray($allAndroidDiamond);
                    $allAndroidDiamond = empty($allAndroidDiamond[0]['androidDiamond']) ? 0 : $allAndroidDiamond[0]['androidDiamond'];
                    
                    //房间名称
                    $r['room_name'] = isset($roomInfoArr[$r['room_id']]) ? $roomInfoArr[$r['room_id']]['name'] : '音洛聊天室';
                    //公会名称
                    if(isset($unionRoomArr[$r['room_id']]['un_id'])){
                        $r['union_name'] = isset($uninInfoArr[$unionRoomArr[$r['room_id']]['un_id']]) ? $uninInfoArr[$unionRoomArr[$r['room_id']]['un_id']]['name'] : '无公会';
                        //公会id
                        $r['unionId'] = isset($uninInfoArr[$unionRoomArr[$r['room_id']]['un_id']]) ? $uninInfoArr[$unionRoomArr[$r['room_id']]['un_id']]['id'] : 0;
                        //公会长id
                        $r['un_user_id'] = isset($uninInfoArr[$unionRoomArr[$r['room_id']]['un_id']]) ? $uninInfoArr[$unionRoomArr[$r['room_id']]['un_id']]['un_user_id'] : 0;
                    }else{
                        $r['union_name'] = '无公会';
                        $r['unionId'] = 0;
                        $r['un_user_id'] = 0;
                    }
                    
                    //安卓人数
                    $r['AndroidNum'] = $useNumAndroid;
                    //安卓消耗总钻石
                    $r['AndroidDiamond'] = $allAndroidDiamond;
                    //ios人数
                    $r['iosNum'] = abs($r['totalPeople'] - $useNumAndroid);
                    //ios消耗总钻石
                    $r['iosDiamond'] = $r['totalDiamond'] - $allAndroidDiamond;
                    $r['date'] = date('Y-m-d', $r['change_timestamp']);
                    $r['created_at'] = Carbon::now()->toDateTimeString();
                    unset($r['change_timestamp']);
                }
                //入库处理
                DB::table($this->dailyRoomDataTable)->insert($alldata);
            }catch (\Exception $e) {
                write_log($this->errorLog, "ERROR 错误日志", ['line'=>$e->getLine(),'msg'=>$e->getMessage()]);
            }
        }else{
            write_log($this->errorLog,'无数据',$alldata);
        }
        
    }

}